﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Excel = Microsoft.Office.Interop.Excel;

namespace IT_Pro.Forms
{
    public partial class frmNVThemNhanVien_Import : DevExpress.XtraEditors.XtraForm
    {
        public frmNVThemNhanVien_Import()
        {
            InitializeComponent();
        }

        private void btnKiemTraDuLieu_Click(object sender, EventArgs e)
        {
            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string manv = dt.Rows[i][0].ToString();
                dt.Rows[i][6] = Class.NV_NhanVien.KiemTraNhanVien(manv);
                if (dt.Rows[i][6].ToString() == "0")
                {
                    dt.Rows[i][5] = true;
                }
                else
                {
                    dt.Rows[i][5] = false;
                }
            }
            btnThemNhanVien.Enabled = true;
        }
        DataTable dt = new DataTable();
        private void btnReadFile_Click(object sender, EventArgs e)
        {
            if (txtLinkFile.Text.Length > 0)
            {   
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range range;

                object misValue = System.Reflection.Missing.Value;

                xlApp = new Excel.ApplicationClass();
                xlWorkBook = xlApp.Workbooks.Open(txtLinkFile.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                range = xlWorkSheet.UsedRange;

                if (range.Rows.Count > 0)
                {
                  btnKiemTraDuLieu.Enabled = true;
                  btnClearDL.Enabled = true;
                }
                // string str;
                int rCnt = 0;
                //int stt = 0;
                for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
                {
                    try
                    {
                        if ((xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2 != null) || (xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2.ToString() == ""))
                        {
                            DataRow dr = dt.NewRow();

                            if (xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2 != null)
                            {
                                dr[0] = xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2.ToString();
                            }
                            if (xlWorkSheet.get_Range("B" + rCnt, "B" + rCnt).Value2 != null)
                            {
                                dr[1] = xlWorkSheet.get_Range("B" + rCnt, "B" + rCnt).Value2.ToString();
                            }
                            if (xlWorkSheet.get_Range("C" + rCnt, "C" + rCnt).Value2 != null)
                            {
                                dr[2] = xlWorkSheet.get_Range("C" + rCnt, "C" + rCnt).Value2.ToString();
                            }
                            else
                            {
                                dr[2] = "";
                            }
                            if (xlWorkSheet.get_Range("D" + rCnt, "D" + rCnt).Value2 != null)
                            {
                                dr[3] = xlWorkSheet.get_Range("D" + rCnt, "D" + rCnt).Value2.ToString();
                            }
                            else
                            {
                                dr[3] = "";
                            }
                            if (xlWorkSheet.get_Range("E" + rCnt, "E" + rCnt).Value2 != null)
                            {
                                dr[4] = xlWorkSheet.get_Range("E" + rCnt, "E" + rCnt).Value2.ToString();
                            }
                            else
                            {
                                dr[4] = "";
                            }
                            dr[5] = true;
                            dr[6] = "0";
                            dt.Rows.Add(dr);
                        }
                    }
                    catch { }
                }

                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                gridNhanVien.DataSource = dt;
            }
        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog open = new OpenFileDialog();
            open.Filter = "Excel 2007|*.xlsx| Excel 2003|*.xls";
            open.Title = "Select Excel file";
            open.ShowDialog();
            txtLinkFile.Text = open.FileName;
        }

        private void frmNVThemNhanVien_Import_Load(object sender, EventArgs e)
        {
            dt.Columns.Add("MaNV", Type.GetType("System.String"));
            dt.Columns.Add("TenNV", Type.GetType("System.String"));
            dt.Columns.Add("DienThoaiNB", Type.GetType("System.String"));
            dt.Columns.Add("DienThoaiDD", Type.GetType("System.String"));
            dt.Columns.Add("Mail", Type.GetType("System.String"));
            dt.Columns.Add("Chon", Type.GetType("System.Boolean"));
            dt.Columns.Add("KiemTra", Type.GetType("System.String"));

            LoadDSPhongvaoCBO();
        }

        private void checkAll_CheckedChanged(object sender, EventArgs e)
        {
            if (dt.Rows.Count > 0)
            {
                if (checkAll.Checked)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dt.Rows[i][5] = true;
                    }

                }
                else
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dt.Rows[i][5] = false;
                    }

                }
            }
        }

        private void LoadDSPhongvaoCBO()
        {
            cboPhongBan.Properties.DataSource = Class.NV_PhongBan.HienThiDsPhong();
            cboPhongBan.Properties.ValueMember = "MaPhong";
            cboPhongBan.Properties.DisplayMember = "TenPhong";
        }

        private void btnThemNhanVien_Click(object sender, EventArgs e)
        {
          DialogResult dr = MessageBox.Show(" Bạn có chắc muốn import dữ liệu này vào CSDL ?", "Thong bao ", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dr == DialogResult.Yes)
            {
                if (cboPhongBan.EditValue.ToString().Length < 1)
                {
                    MessageBox.Show(" Bạn chưa chọn phòng ban ");
                    return;
                }
                Class.NV_NhanVien nv = new Class.NV_NhanVien();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i][5].ToString() == Boolean.TrueString)
                    {
                        nv.MaNV = dt.Rows[i][0].ToString();
                        nv.TenNV = dt.Rows[i][1].ToString();
                        nv.DienThoaiNB = dt.Rows[i][2].ToString();
                        nv.DienThoaiDD = dt.Rows[i][3].ToString();
                        nv.Mail = dt.Rows[i][4].ToString();
                        nv.TenPhong = cboPhongBan.EditValue.ToString();
                        try
                        {
                            if (!Class.NV_NhanVien.ThemNhanVien(nv))                           
                            {
                                MessageBox.Show("Mã nhân viên: " + nv.MaNV + " - "+nv.TenNV+"  đã có. vui lòng chọn mã Khác");
                            }
                        }
                        catch
                        {
                            MessageBox.Show("Thêm danh sách nhân viên lỗi !! Hãy chọn lại");
                        }
                    }                    
                }
                MessageBox.Show("Đã hoàn tất công việc Import");

            }     

        }

        private void btnClearDL_Click(object sender, EventArgs e)
        {
            dt.Clear();
            gridNhanVien.DataSource = null;
            btnKiemTraDuLieu.Enabled = false;
            btnThemNhanVien.Enabled = false;
        }

       
       
    }
}